import 'dart:async';
import 'dart:convert';

import 'package:flutter/foundation.dart';
import 'package:flutter/services.dart';
import 'package:ride_rex/article/comment.dart';
import 'package:ride_rex/constant.dart';
import 'package:ride_rex/main/discover/discover_bean.dart';
import 'package:ride_rex/main/home/landmark_bean.dart';
import 'package:sqflite/sqflite.dart';
import 'package:ylm_ui_kit/ylm_ui_kit.dart';

import 'db_constant.dart';
import 'db_interface.dart';

class DbImplements implements DbInterface {
  Database? _database;

  String get _getUserId => CommonStorage().getString(StorageKey.spKeyUserId);

  @override
  Future openDb() async {
    String databasesPath = await getDatabasesPath();
    await _safeOperate(() async {
      _database = await openDatabase(
        '$databasesPath/${_getUserId}_${DbConstant.dbName}',
        version: DbConstant.dbVersion,
        onOpen: (Database db) async {
          _database = db;
          await Future.wait([
            createMineLandmarkTable(),
            createCommentTable(),
            createLikeTable(),
            createUserTable(),
          ]);
          // await db.execute('DROP TABLE IF EXISTS ${DbConstant.user}');
        },
        onCreate: (Database db, int version) async {
          _database = db;
          await Future.wait([
            createMineLandmarkTable(),
            createCommentTable(),
            createLikeTable(),
            createUserTable(),
          ]);
        },
      );
    }, 'open database failed');
  }

  @override
  Future closeDb() async {
    await _safeOperate(() async {
      await _database?.close();
    }, 'close database failed');
  }

  Future _safeOperate(Future Function() operate, String errorMsg) async {
    try {
      await operate();
    } catch (e) {
      if (kDebugMode) {
        print(errorMsg);
      }
    }
  }

  ///创建地标表
  Future createMineLandmarkTable() async {
    await _safeOperate(() async {
      await _database?.execute('''
CREATE TABLE IF NOT EXISTS ${DbConstant.mineLandmark} (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "$jsonKeyLandmarkBeanLandmark" TEXT,
  "$jsonKeyLandmarkBeanMedalIcon" TEXT,
  "$jsonKeyLandmarkBeanMedalBg" TEXT,
  "$jsonKeyLandmarkBeanCoin" INTEGER,
  "$jsonKeyLandmarkBeanDescription" TEXT,
  "$jsonKeyLandmarkBeanUnlock" INTEGER,
  "$jsonKeyLandmarkBeanLandmarkId" TEXT
);
    ''');
    }, 'create landmark table failed');
  }

  ///创建点赞表
  Future createLikeTable() async {
    await _safeOperate(() async {
      await _database?.execute('''
CREATE TABLE IF NOT EXISTS ${DbConstant.like} (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "${ColumnKey.recordId}" TEXT
);
    ''');
    }, 'create like table failed');
  }

  ///创建评论表
  Future createCommentTable() async {
    await _safeOperate(() async {
      await _database?.execute('''
CREATE TABLE IF NOT EXISTS ${DbConstant.comment} (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "$jsonKeyCommentBeanText" TEXT,
  "$jsonKeyCommentBeanAudioFileName" TEXT,
  "$jsonKeyCommentBeanAudioSize" INTEGER,
  "$jsonKeyCommentBeanAudioDuration" INTEGER,
  "$jsonKeyCommentBeanRecordId" TEXT,
  "$jsonKeyCommentBeanSenderName" TEXT,
  "$jsonKeyCommentBeanSenderAvatar" TEXT,
  "$jsonKeyCommentBeanIsLike" TEXT,
  "${ColumnKey.workId}" TEXT
);
    ''');
    }, 'create comment table failed');
  }

  ///创建用户表
  Future createUserTable() async {
    await _safeOperate(() async {
      await _database?.execute('''
CREATE TABLE IF NOT EXISTS ${DbConstant.user} (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "$jsonKeyFeedUserBeanUserId" TEXT,
  "$jsonKeyFeedUserBeanNickname" TEXT,
  "$jsonKeyFeedUserBeanAvatar" TEXT,
  "$jsonKeyFeedUserBeanIsFriend" TEXT,
  "$jsonKeyFeedUserBeanAuthor" TEXT,
  "$jsonKeyFeedUserBeanLocalVideo" TEXT,
  "$jsonKeyFeedUserBeanLocalVideoCover" TEXT,
  "$jsonKeyFeedUserBeanWorks" TEXT,
  "$jsonKeyFeedUserBeanIsVideoLike" TEXT,
  "$jsonKeyFeedUserBeanLandMarks" TEXT,
  "$jsonKeyFeedUserBeanIsBlock" TEXT,
  "$jsonKeyFeedUserBeanVideoTitle" TEXT,
  "$jsonKeyFeedUserBeanVideoId" TEXT,
  "$jsonKeyFeedUserBeanSortId" INTEGER
);
    ''');
    }, 'create user table failed');
  }

  @override
  Future<List<LandmarkBean>> getLandmarkData() async {
    List<LandmarkBean> list = [];

    await _safeOperate(() async {
      await _database?.transaction((txn) async {
        int count = Sqflite.firstIntValue(await txn.rawQuery('SELECT COUNT(*) FROM ${DbConstant.mineLandmark}')) ?? 0;
        if (count == 0) {
          ///存数据
          String result = await rootBundle.loadString('assets/json/landmark.json');
          List json = jsonDecode(result);
          for (var e in json) {
            list.add(LandmarkBean.fromJson(e));
            await txn.insert(DbConstant.mineLandmark, e);
          }
        } else {
          List<Map> maps = await txn.query(DbConstant.mineLandmark);
          for (var e in maps) {
            list.add(LandmarkBean.fromJson(e));
          }
        }
      });
    }, 'get landmark failed');

    return list;
  }

  @override
  Future updateLandmark(LandmarkBean bean) async {
    await _safeOperate(() async {
      await _database?.transaction((txn) async {
        await txn.update(
          DbConstant.mineLandmark,
          bean.toJson(),
          where: '$jsonKeyLandmarkBeanLandmarkId = ?',
          whereArgs: [bean.landmarkId],
        );
      });
    }, 'update landmark failed');
  }

  @override
  Future cancelLike({required String recordId}) async {
    await _safeOperate(() async {
      await _database?.transaction((txn) async {
        var result = await txn.query(
          DbConstant.like,
          where: '${ColumnKey.recordId} = ?',
          whereArgs: [recordId],
        );
        if (result.isNotEmpty) {
          await txn.delete(
            DbConstant.like,
            where: '${ColumnKey.recordId} = ?',
            whereArgs: [recordId],
          );
        }
      });
    }, 'cancel like failed');
  }

  @override
  Future<bool> isLike({required String recordId}) async {
    bool isLike = false;
    await _safeOperate(() async {
      await _database?.transaction((txn) async {
        var result = await txn.query(
          DbConstant.like,
          where: '${ColumnKey.recordId} = ?',
          whereArgs: [recordId],
        );
        isLike = result.isNotEmpty;
      });
    }, 'search like failed');
    return isLike;
  }

  @override
  Future like({required String recordId}) async {
    await _safeOperate(() async {
      await _database?.transaction((txn) async {
        var result = await txn.query(
          DbConstant.like,
          where: '${ColumnKey.recordId} = ?',
          whereArgs: [recordId],
        );
        if (result.isEmpty) {
          await txn.insert(
            DbConstant.like,
            {ColumnKey.recordId: recordId},
          );
        }
      });
    }, 'insert like failed');
  }

  @override
  Future addComment({required CommentBean bean, required String workId}) async {
    await _safeOperate(() async {
      await _database?.transaction((txn) async {
        var result = await txn.query(
          DbConstant.comment,
          where: '${ColumnKey.recordId} = ?',
          whereArgs: [bean.recordId],
        );
        if (result.isEmpty) {
          await txn.insert(
            DbConstant.comment,
            {ColumnKey.workId: workId, ...bean.toJson()},
          );
        }
      });
    }, 'insert comment failed');
  }

  @override
  Future<List<CommentBean>> getCommentList({required String workId}) async {
    List<CommentBean> list = [];
    await _safeOperate(() async {
      await _database?.transaction((txn) async {
        List<Map> maps = await txn.query(
          DbConstant.comment,
          where: '${ColumnKey.workId} = ?',
          whereArgs: [workId],
        );
        for (var e in maps) {
          CommentBean bean = CommentBean.fromJson(e);

          ///查询评论的点赞状态
          var result = await txn.query(
            DbConstant.like,
            where: '${ColumnKey.recordId} = ?',
            whereArgs: [bean.recordId ?? ''],
          );
          bean.isLike = result.isNotEmpty ? '1' : '0';
          list.add(bean);
        }
      });
    }, 'get comment list failed');
    return list;
  }

  @override
  Future saveUserList(List<FeedUserBean> userList) async {
    await _safeOperate(() async {
      await _database?.transaction((txn) async {
        var result = await txn.query(DbConstant.user);
        if (result.isNotEmpty) return;
        for (var e in userList) {
          await txn.insert(DbConstant.user, e.toDbJson());
        }
      });
    }, 'insert user failed');
  }

  @override
  Future<List<FeedUserBean>> getLocalUserList({String? userId}) async {
    Completer<List<FeedUserBean>> completer = Completer();
    List<FeedUserBean> userList = [];
    await _safeOperate(() async {
      await _database?.transaction((txn) async {
        String where = userId.notEmpty ? ' AND $jsonKeyFeedUserBeanUserId = ?' : '';

        var result = await txn.query(
          DbConstant.user,
          where: '$jsonKeyFeedUserBeanIsBlock = ?$where',
          whereArgs: ['0', if (where.notEmpty) userId],
          orderBy: '$jsonKeyFeedUserBeanSortId ASC',
        );
        userList = result.map((e) => FeedUserBean.fromDbJson(e)).toList();

        for (FeedUserBean userBean in userList) {
          ///作品点赞赋值
          for (FeedUserWorkBean workBean in (userBean.works ?? [])) {
            var result = await txn.query(
              DbConstant.like,
              where: '${ColumnKey.recordId} = ?',
              whereArgs: [workBean.workId ?? ''],
            );
            workBean.isLike = result.isNotEmpty ? '1' : '0';
          }

          ///视频点赞赋值
          var result1 = await txn.query(
            DbConstant.like,
            where: '${ColumnKey.recordId} = ?',
            whereArgs: [userBean.localVideo ?? ''],
          );
          userBean.isVideoLike = result1.isNotEmpty;

          ///视频评论列表
          var result2 = await txn.query(
            DbConstant.comment,
            where: '${ColumnKey.workId} = ?',
            whereArgs: [userBean.videoId ?? ''],
          );
          userBean.videoCommentList = result2.map((e) => CommentBean.fromJson(e)).toList();
        }

        completer.complete(userList);
      });
    }, 'query user failed');

    return completer.future;
  }

  @override
  Future followUser({required String userId, required String isFollow}) async {
    await _safeOperate(() async {
      await _database?.transaction((txn) async {
        await txn.update(
          DbConstant.user,
          {jsonKeyFeedUserBeanIsFriend: isFollow},
          where: '$jsonKeyFeedUserBeanUserId = ?',
          whereArgs: [userId],
        );
      });
    }, 'follow user failed');
  }

  @override
  Future blockUser({required String userId, required String isBlock}) async {
    await _safeOperate(() async {
      await _database?.transaction((txn) async {
        await txn.update(
          DbConstant.user,
          {jsonKeyFeedUserBeanIsBlock: isBlock},
          where: '$jsonKeyFeedUserBeanUserId = ?',
          whereArgs: [userId],
        );
      });
    }, 'block user failed');
  }

  @override
  Future<int> getUnlockedLandmarkCount() async {
    Completer<int> completer = Completer();
    await _safeOperate(() async {
      await _database?.transaction((txn) async {
        var result =
            await txn.query(DbConstant.mineLandmark, where: '$jsonKeyLandmarkBeanUnlock = ?', whereArgs: ['1']);
        completer.complete(result.length);
      });
    }, 'get unlocked landmark count failed');
    return completer.future;
  }
}
